package com.sxhuayuan.parking.dao;

import java.util.Date;
import java.util.List;
import java.util.Map;

import com.sxhuayuan.parking.compenent.domain.KeyValue;
import com.sxhuayuan.parking.entity.Member;
import com.sxhuayuan.parking.entity.ParkingLot;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;

import com.sxhuayuan.parking.config.PayStatus;
import com.sxhuayuan.parking.dto.SumDTO;
import com.sxhuayuan.parking.entity.Agent;
import com.sxhuayuan.parking.entity.Order;

public interface OrderDao extends BaseDao<Order, Long> {

	/** 根据自己所生成的订单号查询 */
	Order findBySn(String sn);

	Order findByLotOrderNo(String lotOrderNo);

	/** 根据华贝订单号查询 */
	@Query(value = "select * from hypk_order where trade_no = ?1", nativeQuery = true)
	Order findByTradeNo(String tradeNo);

	/** 根据进场时间查询订单 */
	List<Order> findByEntertimeAndCarPlate(Date entertime, String carPlate);

	/** 根据进场序列查询成功的订单 */
	@Query(value = "select o from Order o where o.uniqueId = ?1 and o.payStatus=1")
	List<Order> findPaidListByUniqueId(Long uniqueId);

	/** 根据车场出口id查询 */
	@Query(value = "select count(id) from hypk_order where exit_id = ?1", nativeQuery = true)
	Long findCountByExit(Long id);

	@Modifying
	@Query(value = "delete from hypk_order where parking_lot_id = ?1", nativeQuery = true)
	void deleteByParkingLotId(Long id);

	@Modifying
	void deleteByParkingLotSn(String sn);

	@Modifying
	void deleteByUniqueId(Long uniqueId);

	@Modifying
	@Query(value = "delete from hypk_order where pay_status=0 and create_date < ?1", nativeQuery = true)
	void clearTimeoutOrders(String endtime);

	/**
	 * 查询指定时间范围内的订单统计
	 *
	 * @param format
	 *            时间格式化参数
	 * @param startTime
	 *            起始时间
	 * @param endTime
	 *            截止时间
	 * @return 统计结果
	 */
	@Query(value = "select DATE_FORMAT(o.paytime,?1) `name`,count(o.id) `value`,sum(o.money) `money` from hypk_order o where o.pay_status=1 and o.paytime>=?2 and o.paytime<=?3 group by `name` order by o.paytime asc", nativeQuery = true)
	List<Map<String, Object>> findCoundByDateTime(String format, String startTime, String endTime);

	/**
	 * 查询指定时间范围内的车场订单统计
	 *
	 * @param startTime
	 *            起始时间
	 * @param endTime
	 *            截止时间
	 * @return 统计结果
	 */
	@Query(value = "select o.parking_lot_name `name`,count(o.id) `value`,sum(o.money) `money` from hypk_order o where o.pay_status=1 and o.paytime>=?1 and o.paytime<=?2 GROUP BY o.parking_lot_name ", nativeQuery = true)
	List<Map<String, Object>> countParkingLotByDateTime(String startTime, String endTime);

	/** 代理 查询指定时间范围内的车场订单统计 */
	@Query(value = "select o.parking_lot_name `name`,count(o.id) `value`,sum(o.money) `money` from hypk_order o join hypk_agent_relation r on o.agent_id = r.child_id where o.pay_status=1 and o.paytime>=?1 and o.paytime<=?2 and r.parent_id=?3 GROUP BY o.parking_lot_name ", nativeQuery = true)
	List<Map<String, Object>> countParkingLotByDateTimeAndAgent(String startTime, String endTime, Long agentId);

	/* 根据时间查询统计成功支付订单 */
	@Query(value = "select  new com.sxhuayuan.parking.dto.SumDTO(count(o.id),sum(o.money)) from Order o where o.payStatus=1 and o.paytime >= ?1 and o.paytime <=?2")
	SumDTO countByPaytime(Date startTime, Date endTime);

	/* 根据时间与代理查询统计成功支付订单 */
	@Query(value = "select  new com.sxhuayuan.parking.dto.SumDTO(count(o.id),sum(o.money)) from Order o join AgentRelation a on o.agentId = a.child.id where a.parent=?3 and o.payStatus=1 and o.paytime >= ?1 and o.paytime <=?2")
	SumDTO countByPaytimeAndAgent(Date startTime, Date endTime, Agent parent);

	/** 根据时间与车场查询订单统计 */
	@Query(value = "select  new com.sxhuayuan.parking.dto.SumDTO(count(o.id),sum(o.money)) from Order o where o.parkingLot.id in(?3) and o.payStatus=1 and o.paytime >= ?1 and o.paytime <=?2")
	SumDTO countByParkingLot(Date startTime, Date endTime, List<Long> parkingLotIds);

	/** 根据代理查询代理前一天交易总流水 */
	@Query(value = "select sum(o.money),count(o.id),sum(if(o.platform=0,o.money,0))," +
			"count(o.platform=0 or null), sum(if(o.platform=1,o.money,0)),count(o.platform=1 or null) " +
			"from hypk_order o left join hypk_agent_relation r on o.agent_id = r.child_id " +
			"where r.parent_id = ?1 and o.pay_status = 1 and o.paytime >= ?2 and o.paytime < ?3", nativeQuery = true)
	List<Object> countProfitStatisticsByAgent(Long agentId, String startTime, String endTime);

	/** 根据车场查询前一天流水 */
	@Query(value = "select sum(if(o.platform=0,o.money,0)), sum(if(o.platform=1,o.money,0)) from hypk_order o where o.parking_lot_id = ?1 and o.pay_status = 1 and o.paytime >= ?2 and o.paytime < ?3", nativeQuery = true)
	List<Object> countByParkingLotAndPaytime(Long parkingLotId, String startTime, String endTime);

	/** 删除出口时把所有相关订单的出口id设置为null */
	@Modifying
	@Query(value = "update Order set exitId = null where exitId = ?1")
	void setExitIdNull(Long exitId);

	/** 查询缴费记录 */
	List<Order> findByUniqueId(long uniqueId);

	/** 根据uniqueId 查询订单数量*/
	@Query(nativeQuery = true, value = "select count(1) from hypk_order where unique_id = ?1")
	int countByUniqueId(Long uniqueId);

	/** 查询符合开发票的订单列表 */
	@Query("select o from Order o where o.sn in(?1) and o.memberId = ?2 and o.parkingLot=?3")
	List<Order> findByOrderSnAndMemberIdAndParkingLot(List<String> ids, Long memberId, ParkingLot parkingLot);

	@Query("select new com.sxhuayuan.parking.compenent.domain.KeyValue(o.parkingLot.name,o.parkingLot.sn) from Order o " +
			"where o.payStatus=1 and o.refundMoney=0 and o.memberId=?1 and o.platform in (0,1) and o.invoiceStatus in(0,2) " +
			"group by o.parkingLot.sn")
	List<KeyValue> findParkingListByMember(Long memberId);

}
